package com.aaa.dao;

import com.aaa.entity.Role;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 角色管理dao实现类
 */
@Repository
public class RoleDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private RowMapper<Role> rowMapper = new RowMapper<Role>() {
        public Role mapRow(ResultSet resultSet, int i) throws SQLException {
            Role role = new Role();
            role.setId(resultSet.getLong("id"));
            role.setName(resultSet.getString("name"));
            role.setCreateDate(resultSet.getString("create_date"));
            return role;
        }
    };

    /**
     * 查询当前页数据
     * @param offset
     * @param limit
     * @return
     */
    public List<Role> listPage(String keyword, Integer offset, Integer limit){
        String sql = "select r.id,r.name,r.create_date from role r where name like ? order by create_date  desc limit ?,? ";
        List<Role> result = this.jdbcTemplate.query(sql, rowMapper,"%"+keyword+"%", offset, limit);
        return  result;
    }

    /**
     * 查询总条数
     * @return
     */
    public Integer count(String keyword){
        String sql = "select count(id) as c from role where name like ? ";
        Integer result = this.jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        },"%"+keyword+"%");
        return  result;
    }


    /**
     * 保存角色 并获取自动生成的主键
     * @param role
     * @return
     */
    public Long save(final Role role){
        final String sql="insert into role (name,create_date) values(?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException{
                PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setString(1, role.getName());
                ps.setString(2, role.getCreateDate());

                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }
    /**
     * 更新角色
     * @param role
     * @return
     */
    public Integer update(Role role){
        String sql = "update role set name=? where id=?";
        int count = this.jdbcTemplate.update(sql, role.getName(),role.getId());
        return count;
    }


    /**
     * 保存角色能操作的模块
     * @param rid
     * @param mid
     * @return
     */
    public Integer saveRoleMenu(Long rid,Long mid){
        String sql = "insert into role_menu(r_id,m_id) values(?,?)";
        int count = this.jdbcTemplate.update(sql,rid,mid);
        return  count;
    }

    /**
     * 删除角色能操作的模块
     * @param rid
     * @return
     */
    public Integer deleteRoleMenu(Long rid){
        String sql = "delete from role_menu where r_id=?";
        int count = this.jdbcTemplate.update(sql,rid);
        return  count;
    }

    /**
     * 查询所有的角色
     * @return
     */
    public List<Role> listAll(){
        String sql = "select r.id,r.name,r.create_date from role r";
        List<Role> result = this.jdbcTemplate.query(sql, rowMapper);
        return  result;
    }

    /**
     * 查询用户具有的角色
     * @param uid
     * @return
     */
    public List<Role> listUserRoles(Long uid) {
        String sql = "select r.id,r.name,r.create_date from role r inner join user_role ur on ur.r_id=r.id where ur.u_id=?";
        List<Role> result = this.jdbcTemplate.query(sql, rowMapper,uid);
        return  result;
    }

}
